﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_GetTodayTaskBySomeone]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_GetTodayTaskBySomeone];
GO
CREATE PROCEDURE [dbo].[sproc_GetTodayTaskBySomeone]
    @Date datetime,
             @Username nvarchar(20),
    @Type int   
--@Type = 1 今日未完成记录
--@Type = 2 历史未完成记录
--@Type = 3 所有完成记录
As

if @Type = 1
begin

select  a.taskID as ID,a.taskID,a.Subject,a.CooperatorList,a.Date,a.IsConfirm ,a.ArrangedBy,a.Type,a.ProjectID,min(a.PeriodID) beginPeriodID,max(a.PeriodID) endPeriodID,a.EndTime from (
Select  UDS_Schedule.*,UDS_Task.Subject,UDS_Task.CooperatorList,UDS_Task.ArrangedBy,UDS_Task.Type,UDS_Task.ProjectID,UDS_Work_Time_Period.Period,UDS_Task.EndTime
    FROM UDS_Schedule,UDS_Task,UDS_Work_Time_Period
    WHERE Username=@Username and [date]=@Date and UDS_Schedule.TaskID=UDS_Task.ID and UDS_Schedule.PeriodID= UDS_Work_Time_Period.ID and isConfirm!=2  and UDS_Task.Attribute=1
) a group by a.TaskID,a.Subject ,a.CooperatorList,a.IsConfirm ,a.Date,a.Endtime,a.ArrangedBy,a.Type,a.ProjectID order by a.EndTime desc


end

if @Type = 2
begin
 select  a.taskID as ID,a.taskID,a.Subject,a.CooperatorList,a.Date,a.IsConfirm ,a.ArrangedBy,a.Type,a.ProjectID,min(a.PeriodID) beginPeriodID,max(a.PeriodID) endPeriodID,a.EndTime from (
Select  UDS_Schedule.*,UDS_Task.Subject,UDS_Task.CooperatorList,UDS_Task.ArrangedBy,UDS_Task.Type,UDS_Task.ProjectID,UDS_Work_Time_Period.Period,UDS_Task.EndTime
    FROM UDS_Schedule,UDS_Task,UDS_Work_Time_Period
    WHERE Username=@Username and [date]<@Date and UDS_Schedule.TaskID=UDS_Task.ID and UDS_Schedule.PeriodID= UDS_Work_Time_Period.ID and  isConfirm!=2  and  UDS_Task.Attribute=1
) a group by a.TaskID,a.Subject ,a.CooperatorList,a.IsConfirm,a.Date,a.EndTime,a.ArrangedBy,a.Type,a.ProjectID order by a.EndTime desc
end

if @Type = 3
begin
select  a.taskID as ID,a.taskID,a.Subject,a.CooperatorList,a.Date,a.IsConfirm ,a.ArrangedBy,a.Type,a.ProjectID,min(a.PeriodID) beginPeriodID,max(a.PeriodID) endPeriodID,a.EndTime from (
Select  UDS_Schedule.*,UDS_Task.Subject,UDS_Task.CooperatorList,UDS_Task.ArrangedBy,UDS_Task.Type,UDS_Task.ProjectID,UDS_Work_Time_Period.Period,UDS_Task.EndTime
    FROM UDS_Schedule,UDS_Task,UDS_Work_Time_Period
    WHERE Username=@Username  and UDS_Schedule.TaskID=UDS_Task.ID and UDS_Schedule.PeriodID= UDS_Work_Time_Period.ID  and isConfirm=2  and  UDS_Task.Attribute=1
) a group by a.TaskID,a.Subject ,a.CooperatorList,a.IsConfirm,a.Date,a.ArrangedBy,a.Type,a.ProjectID,a.EndTime order by TaskID desc
end